'''
Author: duliang thinktanker@163.com
Date: 2024-05-16 23:44:48
LastEditors: duliang thinktanker@163.com
LastEditTime: 2025-08-23 11:29:02
FilePath: 
Description: 
'''
import sqlite3
import time
import wechatsubscribe
from threading import Thread

# list1 = [(233.0, 101.1), (316.9, 282.4), (700.7, 473.1), (299.2, 273.6)]
# list2 = [(127.0, 115.1), (312.3, 273.1), (519.2, 473.7), (297.4, 273.3)]
# list3 = [(129.8, 105.9), (312.5, 274.5), (518.0, 475.7), (296.3, 265.9)]
BASE = 1900


# 定义检查函数
def check_threshold(differences, base_list, threshold=0.3):
    results = []
    index = 0
    for (dx, dy), (base_x, base_y) in zip(differences, base_list):
        threshold_x = base_x * threshold
        threshold_y = base_y * threshold
        is_x_out = (dx) > threshold_x
        is_y_out = (dy) > threshold_y
        if is_x_out or is_y_out:
            results.append(index)
        index += 1
    return results, threshold


# 检查差值
def check_res(list1, list2, list3):
    if list2 and list3:
        # 计算差值
        diff13 = [(round(a1 - a2, 2), round(b1 - b2, 2))
                  for (a1, b1), (a2, b2) in zip(list1, list3)]
        diff23 = [(round(a3 - a2, 2), round(b3 - b2, 2))
                  for (a3, b3), (a2, b2) in zip(list2, list3)]
        result13 = check_threshold(diff13, list3)
        result23 = check_threshold(diff23, list3)
        return result13, result23
    else:
        # 如果均超出1900则报警
        if list1:
            list1_over_base = []
            # 检查list1中超出1900的值和索引
            for i, item in enumerate(list1):
                if item[0] > BASE:
                    list1_over_base.append(i)
            return [list1_over_base, BASE], []
        else:
            return [], []


# def check_vol_res(list1, list2, list3=None, base=1900):
#     # 如果均超出1900则报警
#     if list1 and list2:
#         list1_over_base = any(item[0] > base for item in list1)
#         list2_over_base = any(item[0] > base for item in list2)
#         return list1_over_base, list2_over_base
#     else:
#         return False, False


def save_db_send_notification(openid_list, cur, index, sql, list1, list3,
                              list13_bases, text):
    inserted = False
    now_time = int(time.time())
    # 过滤一点无意义值
    if list3:
        print(
            f"\033[4;33;45m{text}: {list1[index]} is out of {list13_bases} compared to {list3[index]}\033[0m"
        )
        if list1[index][0] > 50 and list3[index][0] > 50:
            cur.execute(
                sql, (f'{index}', int(time.time()),
                      f"[{list1[index]},{list3[index]},{list13_bases}]", text))
            inserted = True
    else:
        if list1[index][0] > 50:
            cur.execute(
                '''SELECT timestamp FROM `warning` WHERE `position`= ? and `type`= ? order by timestamp DESC''',
                (
                    index,
                    text,
                ))
            cur_result = cur.fetchone()
            cur.execute(sql, (f'{index}', now_time,
                              f"[{list1[index]},{list13_bases}]", text))
            inserted = True
            if cur_result:
                # 如果已经存在记录，检查时间间隔是否超过20分钟
                if now_time - cur_result[0] < 1200:
                    inserted = False
    if inserted and openid_list:
        # "desc": "告警时间{{time2.DATA}}备注{{thing3.DATA}告警地点{{thing4.DATA}}告警标题{{thing10.DATA}}告警状态{{phrase22.DATA}}"
        th_list = []
        for openid in openid_list:
            th_list.append(
                Thread(
                    target=wechatsubscribe.send_notification,
                    args=(
                        openid[0],
                        "告警",
                        (
                            #时间
                            time.strftime("%Y-%m-%d %H:%M:%S",
                                          time.localtime()),
                            # 备注
                            f"超限{list13_bases}",
                            # 地点
                            f"{index+1}#机组",
                            # 标题
                            f"{list1[index]}",
                            # 状态
                            f"{text}异常",
                        ),
                        "pages/gongqing/gongqing"),
                    daemon=True))
        for th in th_list:
            th.start()
        for th in th_list:
            th.join()


def save_warning(list1, list2, list3=None, text=""):
    conn = sqlite3.connect(r'./data/warning.db')
    cur = conn.cursor()
    openid_list = get_openids_by_template_id(
        "qskZOdPVPNH8OjS-hHSccZRYR3pWdlKU_8xZSh16Wv8")
    result13, result23 = check_res(list1, list2, list3)
    # print(result13, result23)
    if result13:
        if result13[0]:
            for r in result13[0]:
                save_db_send_notification(
                    openid_list=openid_list,
                    cur=cur,
                    index=r,
                    sql=
                    '''INSERT INTO `warning` (`position`, `timestamp`, `message`,`type`) VALUES (?, ?, ?, ?);''',
                    list1=list1,
                    list3=list3,
                    list13_bases=result13[1],
                    text=text)
            else:
                print("停机状态")
    if result23:
        if result23[0]:
            for r in result23[0]:
                save_db_send_notification(
                    openid_list=openid_list,
                    cur=cur,
                    index=r,
                    sql=
                    '''INSERT INTO `warning` (`position`, `timestamp`, `message`,`type`) VALUES (?, ?, ?, ?);''',
                    list1=list2,
                    list3=list3,
                    list13_bases=result23[1] * 100,
                    text=text)
            else:
                print("停机状态")
    conn.commit()
    conn.close()


def get_openids_by_template_id(template_id):
    conn = sqlite3.connect('./data/users.db')
    cursor = conn.cursor()
    # 获取所有订阅招标的用户openid
    cursor.execute(
        f"SELECT openid FROM users WHERE template_id LIKE '%{template_id}%'"  # 订阅招标的模板id
    )
    openid_list = cursor.fetchall()
    conn.close()
    return openid_list


def save_notice_to_db(position, message, _type, savedb=False):
    '''
    保存通知到数据库
    '''
    if savedb:
        conn = sqlite3.connect('./data/warning.db')
        cursor = conn.cursor()
        cursor.execute(
            '''INSERT INTO `notice` (`position`, `timestamp`, `message`,`type`) VALUES (?, ?, ?, ?);''',
            (position, int(time.time()), message, _type))
        conn.commit()
        conn.close()
    with open(f'./data/miniindex/{_type}.txt', 'w', encoding='utf-8') as f:
        f.write(f"('{position}',{int(time.time())},'{message}','{_type}')")


def check_zb_warning(text: str):
    '''
    检查振摆报警
    '''
    print(f"\033[5;34;46m检查{text}是否异常\033[0m")
    conn = sqlite3.connect(r'.\data\hisdata.db')
    cur = conn.cursor()
    sql1 = f'''SELECT `26`,`27` FROM `data` WHERE `name` in ("JZ1","JZ2","JZ3","JZ4") ORDER BY `time` DESC LIMIT 12;'''
    cur.execute(sql1)
    res = cur.fetchall()
    conn.close()
    list1 = res[:4]
    list2 = res[4:8]
    list3 = res[8:]
    save_warning(list1, list2, list3, text)
    save_notice_to_db('一站', str(list1), '首页振摆')


def check_vol_waring(text: str):
    '''
    检查功率报警
    '''
    print(f"\033[5;34;46m检查{text}是否异常\033[0m")
    conn = sqlite3.connect(r'.\data\hisdata.db')
    cur = conn.cursor()
    sql1 = f'''SELECT `3` FROM `data` WHERE `name` in ("JZ1","JZ2","JZ3","JZ4") ORDER BY `time` DESC LIMIT 4;'''
    cur.execute(sql1)
    res = cur.fetchall()
    conn.close()
    list1 = res
    list2 = None
    list3 = None
    print(res)
    save_warning(list1, list2, list3, text)
    # save_notice_to_db('一站', str(list1), '首页电压')
    save_notice_to_db('一站', str(list1), '首页功率')


if __name__ == '__main__':
    # check_zb_warning()
    check_vol_waring("功率")
    print("\033[0;31;42m这是默认红色字体背景绿色\033[0m")
    print("\033[1;32;43m这是高亮绿色字体背景黄色\033[0m")
    print("\033[4;33;45m这是下划线黄色字体背景紫红色\033[0m")
    print("\033[5;34;46m这是闪烁蓝色字体背景青蓝色\033[0m")
    # print(
    #     get_openids_by_template_id(
    #         "qskZOdPVPNH8OjS-hHSccZRYR3pWdlKU_8xZSh16Wv8"))
